package com.example.carborrow.dao;


import com.example.carborrow.model.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class CustomerDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public CustomerDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }




    private final  static String GET_ALL_CUS_SQL ="SELECT * FROM customer_view ";
    //获取所有客户
    public List<Customer> getAllCus() {
        return jdbcTemplate.query(GET_ALL_CUS_SQL,new CustomerRowMapper());
    }






    private final  static String GET_CUS_BY_NAME_SQL ="SELECT * FROM customer_view where name like ? ";
    //按名字模糊查询客户
    public List<Customer> getCusByName(String name) {
        String sw = "%" +name+"%";
        return jdbcTemplate.query(GET_CUS_BY_NAME_SQL,new CustomerRowMapper(),name);
    }

    private final  static String GET_CUS_BY_PHONE_SQL ="SELECT * FROM customer_view where phone = ? ";
    //按手机号码查询客户
    public Customer getCusByPhone(String phone) {
        try {
            return jdbcTemplate.queryForObject(GET_CUS_BY_PHONE_SQL, new CustomerRowMapper(), phone);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    private final  static String GET_CUS_BY_ID_SQL ="SELECT * FROM customer_view where customerId=? ";
    //按id获取客户
    public Customer getCusById(int id) {
        try {
            return jdbcTemplate.queryForObject(GET_CUS_BY_ID_SQL,new CustomerRowMapper(),id);
        }catch (Exception e){
            return null;
        }

    }


    private final static String ADD_CUS_SQL = "INSERT INTO customer_view (name,phone,password) VALUES (?, ?,?)";
    // 新增客户
    public int addCustomer(String name , String phone , String password) {
        return jdbcTemplate.update(ADD_CUS_SQL, name,phone,password);
    }




    private final static String UPDATE_CUS_SQL ="UPDATE customer_view SET name = ?, phone = ?, vipLevel = ? WHERE customerId = ?";
    // 更新客户信息
    public int updateCustomer(Customer cus) {
        return jdbcTemplate.update(UPDATE_CUS_SQL, cus.getName(), cus.getPhone(), cus.getVipLevel());
    }


    private final static String UPDATE_CUS_BALANCE_SQL ="UPDATE customer_view SET balance=? WHERE customerId = ?";
    // 更新客户钱
    public int updateCustomerBalance(int customerId ,BigDecimal afterBalance) {
        return jdbcTemplate.update(UPDATE_CUS_BALANCE_SQL, afterBalance, customerId);
    }



    private final static String UPDATE_CUS_PAYS_BALANCE_SQL ="UPDATE customer_view SET pays = ? ,balance=? WHERE customerId = ?";
    // 更新客户钱
    public int updateCustomerPaysBalance(int customerId , BigDecimal afterPays, BigDecimal afterBalance) {
        return jdbcTemplate.update(UPDATE_CUS_PAYS_BALANCE_SQL, afterPays, afterBalance, customerId);
    }

    private final static String UPDATE_CUS_PAYS_BALANCE_LEVEL_SQL ="UPDATE customer_view SET pays = ? ,balance=?, vipLevel=? WHERE customerId = ?";
    // 更新客户钱+level
    public int updateCustomerPaysBalanceLevel(int customerId , BigDecimal afterPays, BigDecimal afterBalance, int level) {
        return jdbcTemplate.update(UPDATE_CUS_PAYS_BALANCE_LEVEL_SQL, afterPays, afterBalance,level, customerId);
    }





    private final static String DEL_CUS_SQL="DELETE FROM customer_view WHERE customerId = ?";
    // 删除客户
    public int deleteCustomer(int customerId) {
        return jdbcTemplate.update(DEL_CUS_SQL, customerId);
    }


    private static class CustomerRowMapper implements RowMapper<Customer> {
        @Override
        public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
            Customer cus = new Customer();
            cus.setCustomerId(rs.getInt("customerId"));
            cus.setName(rs.getString("name"));
            cus.setPhone(rs.getString("phone"));
            cus.setVipLevel(rs.getInt("vipLevel"));
            cus.setDiscountRate(rs.getBigDecimal("discountRate"));
            cus.setPassword(rs.getString("password"));
            cus.setPays(rs.getBigDecimal("pays"));
            cus.setBalance(rs.getBigDecimal("balance"));
            return cus;
        }
    }



}
